package com.roc.service.impl.common;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.pojo.Operators;
import com.roc.pojo.ProfitDetail;
import com.roc.service.IExcelExportService;
import com.roc.service.ServiceException;
import com.roc.util.AmtUtil;
import com.roc.util.Constance;
import com.roc.util.DateTime;
import com.roc.util.ExcelUtil;
import com.roc.util.constant.Role;

/**
 * 
 * @author zx-think
 * 
 */
@Service
@Transactional
@SuppressWarnings("all")
public class ExcelExportServiceImpl implements IExcelExportService {
	static Logger logger = Logger.getLogger(ExcelExportServiceImpl.class);

	/**
	 * 生成HSSFWorkbook
	 * @throws ServiceException 
	 */
	public HSSFWorkbook excelExport(String reportName, String[] headArray,
			List<Object> objList) throws ServiceException {

		HSSFWorkbook wb = new HSSFWorkbook();
		ByteArrayOutputStream output = new ByteArrayOutputStream();
		try {

			HSSFSheet sheet = wb.createSheet(reportName);
			HSSFCellStyle top_style = ExcelUtil.initCellStyle(wb,16, false, false); // 首行标题样式对象
			HSSFCellStyle data_style = ExcelUtil.initCellStyle(wb,12, false, true); // 数据标题样式对象
			HSSFCellStyle general_style = ExcelUtil.initCellStyle(wb, 100, true,false); // 普通样式对象
			HSSFRow row = sheet.createRow(0);
			HSSFFont font = wb.createFont();
			HSSFCell cell = row.createCell((short) 0);
			Region region = new Region((short) 0, (short) 0, (short) 0,(short) ((short) headArray.length-1));
			sheet.addMergedRegion(region);
			cell.setCellValue(reportName); // 表格的第一行第一列显示的数据
			cell.setCellStyle(top_style); // 样式，居中

			row = ExcelUtil.createRow(sheet, 1, headArray, data_style, false);
			int objRowCount = headArray.length;
			for (int i = 0; i < objList.size(); i++) {
				Object[] objs = (Object[]) objList.get(i);
				String[] col_i = new String[objRowCount];
				int objRow = objs.length;
				for (int x = 0; x < objRowCount; x++) {
					if(x>=objRow){
						col_i[x] = Constance.Null2EmptyTrim("");
					}else{
						col_i[x] = Constance.Null2EmptyTrim(objs[x]);

					}
				}

				row = ExcelUtil.createRow(sheet, 2 + i, col_i, general_style,
						true);
			}

		} catch (Exception e) {
			logger.error("########"+reportName+"生成EXCEL数据失败######", e);
			throw new ServiceException(reportName+"生成EXCEL数据失败！");
		} 
		
		return wb;

	}


	// static final Logger logger = Logger.getLogger(ExcelPoiUtil.class);
	private static final NumberFormat doubleFormat = new DecimalFormat("#.00");
	private static final NumberFormat integerFormat = DecimalFormat
			.getIntegerInstance();

	/**
	 * 分润确认单
	 * 
	 * @param merchant
	 * @param list
	 * @param remotePath
	 * @param date
	 * @return
	 * @throws Exception
	 */
	public  SXSSFWorkbook exportProfit(
			Operators operators,  List<Object> list,
			String transDate) throws Exception {
		// TODO Auto-generated method stub
		SXSSFWorkbook wb = new SXSSFWorkbook(1000);
		//第一行
			CellStyle style = initStyle(wb, CellStyle.ALIGN_CENTER, "微软雅黑",16);
			style.setVerticalAlignment(CellStyle.ALIGN_CENTER);
		////第二行
			CellStyle style1 =initStyle(wb, CellStyle.VERTICAL_TOP, "宋体",11);
			style1.setWrapText(true);
		
		//第三行
			CellStyle style2 =  initStyle(wb, CellStyle.ALIGN_CENTER, "微软雅黑",12);
		//style2.se
		//第四行
			CellStyle style3 = initStyle(wb, CellStyle.ALIGN_CENTER_SELECTION, "宋体",10);
		//style3.setIndention((short) 2);
			CellStyle style_r = initStyle(wb, CellStyle.ALIGN_RIGHT ,"宋体",10);
		//style_r.setIndention((short) 2);
			CellStyle style_l = initStyle(wb, CellStyle.ALIGN_LEFT, "宋体",10);
		//style_l.setIndention((short) 2);
		//小计样式
			CellStyle cs=initStyle(wb, CellStyle.ALIGN_RIGHT, "宋体",10);
		
		String merchantName = operators.getRealName();
		String bankName = operators.getBankDeposit();
		String bankAcctNo = operators.getBankAcctNo();
		String bankAcctName = operators.getBankAcctName();
		String fpflag=operators.getTaxRegistrationCertificate();
		String date = DateTime.getNow("yyyyMMdd");

		String year=transDate.substring(0, 4);
		String month=transDate.substring(4, 6);
		Sheet sheet = wb.createSheet(year+"年"+month+"月分润确认函");
		int totalColums=10;
		int totalRows=0;
		for (int i = 0; i < totalColums; i++) {   
		    //column width is set in units of 1/256th of a character width   
			if(i==0||i==4){
				sheet.setColumnWidth(i, 256 * 9); 
			}else if(i==1||i==5||i==7||i==8){
				 sheet.setColumnWidth(i, 256 * 10);   
			}else if(i==3){
				 sheet.setColumnWidth(i, 256 * 15);   
			}
			else{
				 sheet.setColumnWidth(i, 256 * 13);   
			}
		   
		}  
		Row row0 = sheet.createRow(totalRows++);
		// fillCell("国银通代理商综合分润明细表", 0, row0);
		row0.setHeightInPoints(30);
		Cell cTitle = row0.createCell(0);
		
		cTitle.setCellValue("平台结算款确认函");
		cTitle.setCellStyle(style);
		//处理空行
		dealCell(totalColums, row0, style);
		
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,
				totalColums-1));
		Row rowInfo = sheet.createRow(totalRows++);
		//row1.setHeightInPoints(125);
		CellStyle infoStyle=initStyle_nobord(wb, CellStyle.VERTICAL_TOP, "宋体",9);
		CellStyle infoStyle_u=initStyle_uline(wb, CellStyle.VERTICAL_TOP, "宋体",9);
		CellStyle infoStyle_left=initStyle_left(wb, CellStyle.VERTICAL_TOP, "宋体",9);
		CellStyle infoStyle_right=initStyle_right(wb, CellStyle.VERTICAL_TOP, "宋体",9);
		CellStyle infoStyle_right_u=initStyle_right_u(wb, CellStyle.VERTICAL_TOP, "宋体",9);
		
		Cell con=rowInfo.createCell(0);
		con.setCellValue("代理商名称：");
		con.setCellStyle(infoStyle_left);
		con=rowInfo.createCell(1);
		String  merchantName1=merchantName;
		if(fpflag!=null && fpflag.equals("1")){
			merchantName1=merchantName+"(自开发票6)";
		}else{
			merchantName1=merchantName+"(自开发票3)";
		}
		con.setCellValue(merchantName1);
		con.setCellStyle(infoStyle_u);
		/*con=rowInfo.createCell(2);
		con.setCellValue("代理商名称：");
		con.setCellStyle(infoStyle);
		con=rowInfo.createCell(3);
		con.setCellValue(merchantName);
		con.setCellStyle(infoStyle_u);*/
		dealCell(2,totalColums-1, rowInfo, infoStyle_u);
		con=rowInfo.createCell(totalColums-1);
		con.setCellValue("");
		con.setCellStyle(infoStyle_right);

		sheet.addMergedRegion(new CellRangeAddress(1,1,1,6));
		 
		rowInfo = sheet.createRow(totalRows++);
		 con=rowInfo.createCell(0);
		
		con.setCellValue("收款信息：");
		con.setCellStyle(infoStyle_left);
		con=rowInfo.createCell(1);
		con.setCellValue("账户名：");
		con.setCellStyle(infoStyle);
		con=rowInfo.createCell(2);
		con.setCellValue(bankAcctName);
		con.setCellStyle(infoStyle_u);
		dealCell(3,5, rowInfo, infoStyle_u);
		sheet.addMergedRegion(new CellRangeAddress(2, 2, 2,
				4));
		dealCell(5,totalColums-1, rowInfo, infoStyle);
		con=rowInfo.createCell(totalColums-1);
		con.setCellValue("");
		con.setCellStyle(infoStyle_right);

		rowInfo = sheet.createRow(totalRows++);
		 con=rowInfo.createCell(0);
		
		con.setCellValue("");
		con.setCellStyle(infoStyle_left);
		con=rowInfo.createCell(1);
		con.setCellValue("开户行：");
		con.setCellStyle(infoStyle);
		con=rowInfo.createCell(2);
		con.setCellValue(bankName);
		con.setCellStyle(infoStyle_u);
		dealCell(3,5, rowInfo, infoStyle_u);
		sheet.addMergedRegion(new CellRangeAddress(3, 3, 2,
				4));
		dealCell(5,totalColums-1, rowInfo, infoStyle);
		con=rowInfo.createCell(totalColums-1);
		con.setCellValue("");
		con.setCellStyle(infoStyle_right);

		rowInfo = sheet.createRow(totalRows++);
		 con=rowInfo.createCell(0);
		
		con.setCellValue("");
		con.setCellStyle(infoStyle_left);
		con=rowInfo.createCell(1);
		con.setCellValue("账号：");
		con.setCellStyle(infoStyle);
		con=rowInfo.createCell(2);
		con.setCellValue(bankAcctNo);
		con.setCellStyle(infoStyle_u);
		dealCell(3,5, rowInfo, infoStyle_u);
		sheet.addMergedRegion(new CellRangeAddress(4, 4, 2,
				4));
		dealCell(5,totalColums-1, rowInfo, infoStyle);
		con=rowInfo.createCell(totalColums-1);
		con.setCellValue("");
		con.setCellStyle(infoStyle_right);

		rowInfo = sheet.createRow(totalRows++);
		 con=rowInfo.createCell(0);
		
		con.setCellValue(year+"年_"+month+"_月应结算总金额");
		con.setCellStyle(infoStyle_left);
		con=rowInfo.createCell(1);
		con.setCellValue("");
		con.setCellStyle(infoStyle);
		sheet.addMergedRegion(new CellRangeAddress(5,5,0,1));
		con=rowInfo.createCell(2);
		con.setCellValue("人民币小写：");
		con.setCellStyle(infoStyle);
		Cell conAmt=rowInfo.createCell(3);
		conAmt.setCellValue("");
		conAmt.setCellStyle(infoStyle_u);
		con=rowInfo.createCell(4);
		con.setCellValue("");
		con.setCellStyle(infoStyle_u);
		sheet.addMergedRegion(new CellRangeAddress(5, 5, 3,
				4));
		con=rowInfo.createCell(5);
		con.setCellValue("确认签字：");
		con.setCellStyle(infoStyle);
		dealCell(6,totalColums-1, rowInfo, infoStyle);
		con=rowInfo.createCell(totalColums-1);
		con.setCellValue("");
		con.setCellStyle(infoStyle_right);
		
		
		rowInfo = sheet.createRow(totalRows++);
		 con=rowInfo.createCell(0);
		
		con.setCellValue("");
		con.setCellStyle(infoStyle_left);
		con=rowInfo.createCell(1);
		con.setCellValue("");
		con.setCellStyle(infoStyle);
		
		con=rowInfo.createCell(2);
		con.setCellValue("人民币大写：");
		con.setCellStyle(infoStyle);
		Cell conAmtBig=rowInfo.createCell(3);
		conAmtBig.setCellValue("");
		conAmtBig.setCellStyle(infoStyle_u);
		con=rowInfo.createCell(4);
		con.setCellValue("");
		con.setCellStyle(infoStyle_u);
		sheet.addMergedRegion(new CellRangeAddress(6, 6, 3,
				4));
		con=rowInfo.createCell(5);
		con.setCellValue("盖    章：");
		con.setCellStyle(infoStyle);
		dealCell(6,totalColums-1, rowInfo, infoStyle);
		con=rowInfo.createCell(totalColums-1);
		con.setCellValue("");
		con.setCellStyle(infoStyle_right);
		
		//
		
		rowInfo = sheet.createRow(totalRows++);
		con=rowInfo.createCell(0);
		con.setCellValue("");
		con.setCellStyle(infoStyle_left);
		dealCell(1,totalColums-1, rowInfo, infoStyle);
		con=rowInfo.createCell(totalColums-1);
		con.setCellValue("");
		con.setCellStyle(infoStyle_right);
		//汇总标题
		Row row_hun = sheet.createRow(totalRows++);
		Cell c_conHun = row_hun.createCell(0);
		
		c_conHun.setCellValue("   "+year+" 年  __"+month+"__ 月 平台业务结算汇总表 ");
		c_conHun.setCellStyle(style2);
		dealCell(totalColums, row_hun, style2);
		
		sheet.addMergedRegion(new CellRangeAddress(8, 8, 0,
				totalColums-1));
		
		
		//正常交易标题
		Row row_zheng = sheet.createRow(totalRows++);
		Cell c_zheng = row_zheng.createCell(0);
		
		c_zheng.setCellValue("   正常交易数据   ");
		c_zheng.setCellStyle(style2);
		dealCell(totalColums, row_zheng, style2);
		
		sheet.addMergedRegion(new CellRangeAddress(9, 9, 0,
				totalColums-1));
		
		///正常交易数据
		Row row2 = sheet.createRow(totalRows++);
		String[] titls = { "序号", "交易类型", "卡类型", "总笔数", "总金额(元)", "实际分润(元)"};
		int j=0;
		int b=0;
		for (int k = 0; k <titls.length; k++) {
			Cell c=null;
			
			if(k==0){
				c = row2.createCell(b);
				c.setCellValue(titls[k]);
				
				c.setCellStyle(style2);
			}
			if(k==1){
				c = row2.createCell(b+1);
				c.setCellValue(titls[k]);
				
				c.setCellStyle(style2);
				c =row2.createCell(b+2);
				c.setCellValue("");
				c.setCellStyle(style2);
			    sheet.addMergedRegion(new CellRangeAddress(10,10,1,2));
			}
			if(k==2){
				c = row2.createCell(b+3);
				c.setCellValue(titls[k]);
				
				c.setCellStyle(style2);
				c =row2.createCell(b+4);
				c.setCellValue("");
				c.setCellStyle(style2);
			    sheet.addMergedRegion(new CellRangeAddress(10,10,3,4));
			    j=j+1;
			}
			if(k==3){
				c = row2.createCell(b+5);
				c.setCellValue(titls[k]);
				
				c.setCellStyle(style2);
			}
			if(k==4){
				c = row2.createCell(b+6);
				c.setCellValue(titls[k]);
				
				c.setCellStyle(style2);
				c =row2.createCell(b+7);
				c.setCellValue("");
				c.setCellStyle(style2);
			    sheet.addMergedRegion(new CellRangeAddress(10,10,6,7));
			    j=j+2;
			}
			if(k==5){
				c = row2.createCell(b+8);
				c.setCellValue(titls[k]);
				
				c.setCellStyle(style2);
				c =row2.createCell(b+9);
				c.setCellValue("");
				c.setCellStyle(style2);
			    sheet.addMergedRegion(new CellRangeAddress(10,10,8,9));
			    
			}
			
			//b=b+2;
		}
		double sumAmt = 0.0;
		double sumProfit = 0.0;
		long count = 0;
		int g=0;
		//totalRows++;
		if(list.size()>0 && list!=null){
			int i=0;
			for (int k=0;k<list.size();k++) {
				ProfitDetail pro= (ProfitDetail) list.get(k);
				Row row = sheet.createRow(i + totalRows);
				Cell c = row.createCell(g);
				c.setCellValue(String.valueOf(i+1));c.setCellStyle(style3);
				Cell c1 = row.createCell(g+1);
				c1.setCellValue(pro.getProdCd());
				c1.setCellStyle(style_l);
				c1 = row.createCell(g+2);
				c1.setCellValue("");
				c1.setCellStyle(style_l);
				sheet.addMergedRegion(new CellRangeAddress(i+totalRows,i+totalRows,1,2));
				Cell c2 = row.createCell(g+3);
				c2.setCellValue(pro.getCardClass());
				c2.setCellStyle(style_l);
				c2 = row.createCell(g+4);
				c2.setCellValue("");
				c2.setCellStyle(style_l);
				sheet.addMergedRegion(new CellRangeAddress(i+totalRows,i+totalRows,3,4));
				
				Cell c3 = row.createCell(g+5);
				c3.setCellValue(pro.getTCount());
				c3.setCellStyle(style_r);
				
				Cell c4 = row.createCell(g+6);
				c4.setCellValue(String.format("%.2f", pro.getSAmt()));
				c4.setCellStyle(style_r);
				c4 = row.createCell(g+7);
				c4.setCellValue("");
				c4.setCellStyle(style_r);
				sheet.addMergedRegion(new CellRangeAddress(i+totalRows,i+totalRows,6,7));
				
				Cell c5 = row.createCell(g+8);
				double profitTotal=Double.valueOf(String.format("%.2f",pro.getRealMerfee()));
				/*if(profitTotal<0){
					profitTotal=0;
				}*/
				c5.setCellValue(String.format("%.2f",profitTotal));
				c5.setCellStyle(style_r);
				c5 = row.createCell(g+9);
				c5.setCellValue("");
				c5.setCellStyle(style_r);
				sheet.addMergedRegion(new CellRangeAddress(i+totalRows,i+totalRows,8,9));


			
			
				count += NumberUtils.toInt(pro.getTCount());
				sumAmt += NumberUtils.toDouble(ObjectUtils.toString(pro.getSAmt()));
				sumProfit += NumberUtils.toDouble(ObjectUtils.toString(pro.getRealMerfee()));
				i++;
			}
			
		}
		
		totalRows=list.size() +totalRows;
		
			Row rowLast = sheet.createRow(totalRows);
			Cell c_last=rowLast.createCell(0);
			c_last.setCellValue("小计 :");
			c_last.setCellStyle(cs);
			dealCell(5, rowLast,cs );
			sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows,0,4)); 
			
			Cell clast1=rowLast.createCell(5);
			clast1.setCellValue(count);
			clast1.setCellStyle(cs);
			
			Cell clast2=rowLast.createCell(6);
			clast2.setCellValue(format(sumAmt, Double.class));
			clast2.setCellStyle(cs);
			clast2=rowLast.createCell(7);
			clast2.setCellValue("");
			clast2.setCellStyle(cs);
			sheet.addMergedRegion(new CellRangeAddress(totalRows, totalRows, 6,7));
			
			Cell clast3=rowLast.createCell(8);
			clast3.setCellValue(format(sumProfit, Double.class));
			clast3.setCellStyle(cs);
			clast3=rowLast.createCell(9);
			clast3.setCellValue("");
			clast3.setCellStyle(cs);
			sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows, 8,9));
			totalRows++;
			rowInfo = sheet.createRow(totalRows);
			con=rowInfo.createCell(0);
			con.setCellValue("");
			con.setCellStyle(infoStyle_left);
			dealCell(1,totalColums-1, rowInfo, infoStyle);
			con=rowInfo.createCell(totalColums-1);
			con.setCellValue("");
			con.setCellStyle(infoStyle_right);
			totalRows++;
			//强扣数据标题
			Row row_qiang = sheet.createRow(totalRows);
			Cell c_conqiang = row_qiang.createCell(0);
			
			c_conqiang.setCellValue("   强扣数据   ");
			c_conqiang.setCellStyle(style2);
			dealCell(totalColums, row_qiang,style2 );
			sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows, 0,
					totalColums-1));
			totalRows++;
			Row qiangLast = sheet.createRow(totalRows);
			String[] qiangKouMin = { "商户号", "商户名称（终端号）","", "交易日期", "主交易卡号", "交易金额", "强扣金额",
					"银行扣款日期", "已扣金额", "实扣金额" };
			double qkAmt=0;
			int y=0 ;int x=0;
			for (int k = 0; k < qiangKouMin.length; k++) {
				Cell c = qiangLast.createCell(x);
				c.setCellValue(qiangKouMin[k]);
				if(k==2){
					sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows, 1,
							2));
				}
				c.setCellStyle(style2);
				x=x+1;
			}
			totalRows++;
			Row qiangLast1 = sheet.createRow(totalRows);
			String[] qiangKouMin1= { " ", " ", " ", " ", " ", " "," "," "," ","" };
			int yy=0 ;int xx=0;
			for (int k = 0; k < qiangKouMin1.length; k++) {
				if(k==2){
					sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows, 1,
							2));
				}
				Cell c = qiangLast1.createCell(xx);
				c.setCellValue(qiangKouMin1[k]);
				
				c.setCellStyle(style3);
				xx++;
			}
			totalRows++;
			Row rowLastend = sheet.createRow(totalRows);
			Cell c_lastend=rowLastend.createCell(0);
			c_lastend.setCellValue("小计 :");
			c_lastend.setCellStyle(cs);
			dealCell(totalColums-1, rowLastend,cs );
			sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows,0,totalColums-2)); 
			
			Cell c_lastend1=rowLastend.createCell(totalColums-1);
			c_lastend1.setCellValue("");
			c_lastend1.setCellStyle(style3);
			totalRows++;
			Row rowlast_1 = sheet.createRow(totalRows);
			rowlast_1.setHeightInPoints(25);
			Cell c_cons = rowlast_1.createCell(0);
			c_cons.setCellValue("本月结算款=正常结算资金：");
			c_cons.setCellStyle(infoStyle_left);
			c_cons = rowlast_1.createCell(1);
			c_cons.setCellValue("");
			c_cons.setCellStyle(infoStyle);
			sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows, 0,
					1));
			c_cons = rowlast_1.createCell(2);
			if(fpflag==null || fpflag.equals("1")){
				sumProfit=sumProfit;
			}else{
				sumProfit=sumProfit-(sumProfit/1.03-sumProfit/1.06);
			}
			c_cons.setCellValue(AmtUtil.amtFormat(sumProfit));
			c_cons.setCellStyle(infoStyle_u);
			c_cons = rowlast_1.createCell(3);
			c_cons.setCellValue("元-本月强扣资金：");
			c_cons.setCellStyle(infoStyle);
			c_cons = rowlast_1.createCell(4);
			c_cons.setCellValue(AmtUtil.amtFormat(qkAmt));
			c_cons.setCellStyle(infoStyle_u);
			c_cons = rowlast_1.createCell(5);
			c_cons.setCellValue("元=实际应结算总金额：");
			c_cons.setCellStyle(infoStyle);
			c_cons = rowlast_1.createCell(6);
			c_cons.setCellValue(AmtUtil.amtFormat(sumProfit-qkAmt)+" 元");
			
			
		
			c_cons.setCellStyle(infoStyle_u);
			c_cons = rowlast_1.createCell(7);
			c_cons.setCellValue("");
			c_cons.setCellStyle(infoStyle);
			c_cons = rowlast_1.createCell(8);
			c_cons.setCellValue("");
			c_cons.setCellStyle(infoStyle);
			c_cons = rowlast_1.createCell(9);
			c_cons.setCellValue("");
			c_cons.setCellStyle(infoStyle_right);
			
			
			/*if(loginMerchant==null||Constance.MERCHANT_TYPE_CAIWU.equals(loginMerchant.getMerType())||
					Constance.MERCHANT_TYPE_SYSTEM.equals(loginMerchant.getMerType())){
					
				if("Y".equals(merchant.getFapiaoFlag())){
					conAmt.setCellValue(AmtUtil.amtFormat(sumProfit));
					conAmtBig.setCellValue(AmtUtil.change(sumProfit));
				}else if("Z".equals(merchant.getFapiaoFlag())){
					conAmt.setCellValue(AmtUtil.amtFormat(sumProfit*Constance.FAPIAO_FEE_3));
					conAmtBig.setCellValue(AmtUtil.change(sumProfit*Constance.FAPIAO_FEE_3));
				}else if("X".equals(merchant.getFapiaoFlag())){
					conAmt.setCellValue(AmtUtil.amtFormat(sumProfit));
					conAmtBig.setCellValue(AmtUtil.change(sumProfit));
				}else{
					conAmt.setCellValue(AmtUtil.amtFormat(sumProfit*Constance.FAPIAO_FEE_6));
					conAmtBig.setCellValue(AmtUtil.change(sumProfit*Constance.FAPIAO_FEE_6));
				}
			}else{*/
				conAmt.setCellValue(AmtUtil.amtFormat(sumProfit));
				conAmtBig.setCellValue(AmtUtil.change(sumProfit));
			//}
			
			
			
			totalRows++;
			
			
			Row rowlast_1_0 = sheet.createRow(totalRows);
			rowlast_1_0.setHeightInPoints(5);
			Cell con_0=rowlast_1_0.createCell(0);
			con_0.setCellValue(" ");
			con_0.setCellStyle(infoStyle_left);
			dealCell(1,totalColums-1, rowlast_1_0, infoStyle);
			con_0=rowlast_1_0.createCell(totalColums-1);
			con_0.setCellValue(" ");
			con_0.setCellStyle(infoStyle_right);
			totalRows++;
			
		/*	if(loginMerchant==null||((Constance.MERCHANT_TYPE_CAIWU.equals(loginMerchant.getMerType())||
					Constance.MERCHANT_TYPE_SYSTEM.equals(loginMerchant.getMerType())))){*/
				//倒数1
				Row rowlast_2 = sheet.createRow(totalRows);
				// fillCell("国银通代理商综合分润明细表", 0, row0);
				rowlast_2.setHeightInPoints(110);
				Cell c_cons1 = rowlast_2.createCell(0);
				/*String isNeeded=merchant.getFapiaoFlag();
				if("Y".equals(merchant.getFapiaoFlag())){
					c_cons1.setCellValue("如以上信息确认无误，请在收到此函五个工作日内盖章确认，并以传真或电子扫描件方式邮件回传至我司，如逾期不予确认，当期平台结算款将延至次月进行结算。《确认函》原件及发票请在当月10日前寄回我司。如我司于当月15日前未收到《确认函》原件及发票，次月的结算款将在次月收到上个月及当月的《确认函》原件及发票后再行支付。  \n       谢谢配合！ \n                                       银嘉(上海)金融信息服务有限公司  \n                                           "+date.substring(0,4)+"  年   "+date.substring(4,6)+"   月   "+date.substring(6,8)+"   日      \n    ");
					
				}else{
				*/
				if(Role.AGENT_BUSS.getCode().equals(operators.getRoleType())||Role.AGENT.getCode().equals(operators.getRoleType())){
					c_cons1.setCellValue("\n "+date.substring(0,4)+"  年   "+date.substring(4,6)+"   月   "+date.substring(6,8)+"   日      \n    ");
	
				}else{
					c_cons1.setCellValue("    如以上信息确认无误，请在收到此函五个工作日内盖章确认，并以传真或电子扫描件方式邮件回传至我司，如逾期不予确认，当期平台结算款将延至次月进行结算。《确认函》原件请在当月10日前寄回我司。如我司于当月15日前未收到《确认函》原件，次月的结算款将在次月收到上个月及当月的《确认函》原件后再行支付。  \n       谢谢配合！ \n                                       上海德颐网络技术有限公司  \n                                           "+date.substring(0,4)+"  年   "+date.substring(4,6)+"   月   "+date.substring(6,8)+"   日      \n    ");
				}
				//}
				c_cons1.setCellStyle(style1);
				dealCell(totalColums, rowlast_2,style1 );
				sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows, 0,
						totalColums-1));
		/*	}else{
				//倒数1
				Row rowlast_2 = sheet.createRow(totalRows);
				CellStyle s =initStyle(wb, CellStyle.ALIGN_RIGHT, "宋体",12);
				style1.setWrapText(true);
				// fillCell("国银通代理商综合分润明细表", 0, row0);
				rowlast_2.setHeightInPoints(20);
				Cell c_cons1 = rowlast_2.createCell(0);
				c_cons1.setCellValue(""+date.substring(0,4)+"  年   "+date.substring(5,6)+"   月   "+date.substring(7,8)+"   日       ");
				c_cons1.setCellStyle(s);
				dealCell(totalColums, rowlast_2,s );
				sheet.addMergedRegion(new CellRangeAddress(totalRows,totalRows, 0,
						totalColums-1));
			}*/
			totalRows++;
			PrintSetup ps = sheet.getPrintSetup();   
		    //设置自动换行   
		    sheet.setAutobreaks(true);   
		       
		    ps.setFitHeight((short) 1);   
		    ps.setFitWidth((short) 1);   

		return wb;
	}


	private static void dealCell(int cellNum,Row row,CellStyle style){
		for(int i=1;i<cellNum;i++){
			Cell	cTitle = row.createCell(i);
			cTitle.setCellValue("");
			cTitle.setCellStyle(style);
		}
	}
	private static void dealCell(int start,int cellNum,Row row,CellStyle style){
		for(int i=start;i<cellNum;i++){
			Cell	cTitle = row.createCell(i);
			cTitle.setCellValue("");
			cTitle.setCellStyle(style);
		}
	}
	private  static String format(Object o, Class c) {
		String result = "";
		if (o == null) {
			result = "";
		} else if (Double.class.equals(c)) {
			result = doubleFormat.format(o);
		} else if (Integer.class.equals(c)) {
			result = integerFormat.format(o);
		} else {
			result = ObjectUtils.toString(o);
		}
		return result;
	}

	private  static CellStyle initStyle(SXSSFWorkbook wb,short alignType,String fontName,int fontSize){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		
		Font font= wb.createFont();
		font.setFontHeightInPoints((short)fontSize);
		//font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short)9);// 设置背景色  
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框  
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框  
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框  
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		
		return style;
	}
	private static CellStyle initStyle_uline(SXSSFWorkbook wb,short alignType,String fontName,int fontSize){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font= wb.createFont();
		font.setFontHeightInPoints((short)fontSize);
		//font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short)9);// 设置背景色  
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框  
		style.setBorderLeft(HSSFCellStyle.BORDER_NONE);//左边框  
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);//上边框  
		style.setBorderRight(HSSFCellStyle.BORDER_NONE);//右边框 
		
		return style;
	}
	private  static CellStyle initStyle_right_u(SXSSFWorkbook wb,short alignType,String fontName,int fontSize){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font= wb.createFont();
		font.setFontHeightInPoints((short)fontSize);
		//font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short)9);// 设置背景色  
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框  
		style.setBorderLeft(HSSFCellStyle.BORDER_NONE);//左边框  
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);//上边框  
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		
		return style;
	}
	private  static CellStyle initStyle_nobord(SXSSFWorkbook wb,short alignType,String fontName,int fontSize){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font= wb.createFont();
		font.setFontHeightInPoints((short)fontSize);
		//font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short)9);// 设置背景色  
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		style.setBorderBottom(HSSFCellStyle.BORDER_NONE); //下边框  
		style.setBorderLeft(HSSFCellStyle.BORDER_NONE);//左边框  
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);//上边框  
		style.setBorderRight(HSSFCellStyle.BORDER_NONE);//右边框 
		
		return style;
	}
	private  static CellStyle initStyle_left(SXSSFWorkbook wb,short alignType,String fontName,int fontSize){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font= wb.createFont();
		font.setFontHeightInPoints((short)fontSize);
		//font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short)9);// 设置背景色  
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		style.setBorderBottom(HSSFCellStyle.BORDER_NONE); //下边框  
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框  
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);//上边框  
		style.setBorderRight(HSSFCellStyle.BORDER_NONE);//右边框 
		
		return style;
	}
	private  static CellStyle initStyle_right(SXSSFWorkbook wb,short alignType,String fontName,int fontSize){
		CellStyle style = wb.createCellStyle();
		style.setAlignment(alignType);
		Font font= wb.createFont();
		font.setFontHeightInPoints((short)fontSize);
		//font.setUnderline(Font.U_SINGLE);
		font.setFontName(fontName);
		style.setFont(font);
		style.setFillForegroundColor((short)9);// 设置背景色  
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
		style.setBorderBottom(HSSFCellStyle.BORDER_NONE); //下边框  
		style.setBorderLeft(HSSFCellStyle.BORDER_NONE);//左边框  
		style.setBorderTop(HSSFCellStyle.BORDER_NONE);//上边框  
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		
		return style;
	}



}
